using System.Data;
using mapshiftApplication.Dto;
using Oracle.ManagedDataAccess.Client;

namespace mapshiftApplication.Helpers;

public class SqlHelper
{
    // Sort 白名单，避免 SQL 注入
    private static readonly Dictionary<string, string> SortColumnMap = new(StringComparer.OrdinalIgnoreCase)
    {
        ["id"] = "ID",
        ["createdAt"] = "CREATED_AT",
        ["completedAt"] = "COMPLETED_AT",
        ["status"] = "STATUS",
        ["category"] = "CATEGORY",
        ["proposerDept"] = "PROPOSER_DEPT"
    };

    public static (string whereSql, List<OracleParameter> parameters) BuildWhere(IssueQuery q)
    {
        var cond = new List<string> { "IS_DELETED = 'N'" };
        var ps = new List<OracleParameter>();

        if (!string.IsNullOrWhiteSpace(q.Keyword))
        {
            cond.Add("(UPPER(SUBJECT) LIKE :P_KW OR UPPER(DESCRIPTION) LIKE :P_KW OR UPPER(PROPOSER) LIKE :P_KW)");
            ps.Add(new OracleParameter("P_KW", OracleDbType.Varchar2, $"%{q.Keyword.Trim().ToUpperInvariant()}%", ParameterDirection.Input));
        }

        if (!string.IsNullOrWhiteSpace(q.Owner))
        {
            cond.Add("UPPER(OWNER) LIKE :P_Owner");
            ps.Add(new OracleParameter("P_Owner", OracleDbType.Varchar2, $"%{q.Owner.Trim().ToUpperInvariant()}%", ParameterDirection.Input));
        }
        
        if (!string.IsNullOrWhiteSpace(q.ProposerDept))
        {
            cond.Add("PROPOSER_DEPT = :P_PDEPT");
            ps.Add(new OracleParameter("P_PDEPT", OracleDbType.Varchar2, q.ProposerDept, ParameterDirection.Input));
        }

        if (!string.IsNullOrWhiteSpace(q.OwnerDept))
        {
            cond.Add("OWNER_DEPT = :P_ODPT");
            ps.Add(new OracleParameter("P_ODPT", OracleDbType.Varchar2, q.OwnerDept, ParameterDirection.Input));
        }

        if (!string.IsNullOrWhiteSpace(q.Category))
        {
            cond.Add("CATEGORY = :P_CAT");
            ps.Add(new OracleParameter("P_CAT", OracleDbType.Varchar2, q.Category, ParameterDirection.Input));
        }
        if (!string.IsNullOrWhiteSpace(q.Status))
        {
            cond.Add("STATUS = :P_STATUS");
            ps.Add(new OracleParameter("P_STATUS", OracleDbType.Varchar2, q.Status, ParameterDirection.Input));
        }
        if (q.FromDate.HasValue)
        {
            cond.Add("TRUNC(CREATED_AT) >= TRUNC(:P_FROM)");
            ps.Add(new OracleParameter("P_FROM", OracleDbType.Date, q.FromDate.Value, ParameterDirection.Input));
        }
        if (q.ToDate.HasValue)
        {
            cond.Add("TRUNC(CREATED_AT) <= TRUNC(:P_TO)");
            ps.Add(new OracleParameter("P_TO", OracleDbType.Date, q.ToDate.Value, ParameterDirection.Input));
        }

        var whereSql = cond.Count > 0 ? "WHERE " + string.Join(" AND ", cond) : "";
        return (whereSql, ps);
    }

    public static string BuildOrderBy(string? sortBy, string? sortDir)
    {
        var dir = (string.Equals(sortDir, "desc", StringComparison.OrdinalIgnoreCase)) ? "DESC" : "ASC";
        if (!string.IsNullOrWhiteSpace(sortBy) && SortColumnMap.TryGetValue(sortBy!, out var col))
            return $"ORDER BY {col} {dir}";
        return "ORDER BY ID ASC";
    }
}